Question

I need help making a formula to find cost with the objectives. the "another worksheet" is the catalog excel sheet.

Objectives - After completing this lab, you will be able to: 1. Look up information from another worksheet 2. Use IF() and ISBLANK() to avoid calculations when there is no data 3. Handle missing data with IFNA) Purchase Order Client Name: Date: P.O. Number John Doe 22-Jan-20 97865 Total Cost 202 Catalog Number Quantity Description 201 2 CONN 2P RCPT IP68 4 PICO-EZMATE HARNESS FOR VERO 6" 203 6 LED SOCKET HORIZONTAL PCB 8 FILAMENT BULB 1 LED INDICATOR 0.5W RED 4PLCC 3 LED BLUE 465NM WTR CLR SMD 207 5 ED XLAMP MLE AMBER 590NM 4SMD 208 9 LED GREEN TI RIGHT ANGLE PCB 10 LED XLAMP NEUTRAL WHT 4500K 2SMD 11 LAMP INCAND RT-1 WIRE TERM 12V 211 1 LAMP INCAND RT-1.75 BI-PIN 14V 2 LAMP INCAN RT-1.75 WIRE TERM 28V 213 13 LAMP INCAND RT-1.75 BI-PIN 28V 214 14 505NM 30DEGREE 5MM PLASTIC 15 LED BLUE-GREEN CLEAR T-1 3/4 T/H 10 LED BLUE-GREEN DIFF ICE CUBE T/H 19 LED GREEN/RED/YLW CLR 4PLCC SMD 218 3 LED GREEN/RED/YLW CLR 4PLCC SMD 219 4 LED SUNPLUS 35 PURPLE SMD 220 5 LED LUXEON REBEL LIME SMD Unit Price Cost $1.99 $1.14 $1.63 $6.26 $1.87 $2.11 $1.10 $1.07 $1.24 $1.26 $2.31 $1.07 $1.89 $2.00 209 210 212 215 216 217 $1.07 $1.31 $2.25 $1.70 $3.94 Catalog Catalog Number Description 201 CONN 2P RCPT IP68 202 PICO-EZMATE HARNESS FOR VERO 6" 203 LED SOCKET HORIZONTAL PCB 204 FILAMENT BULB 205 LED INDICATOR 0.5W RED 4PLCC 206 LED BLUE 465NM WTR CLR SMD 207 ED XLAMP MLE AMBER 590NM 4SMD 208 LED GREEN T1 RIGHT ANGLE PCB 209 LED XLAMP NEUTRAL WHT 4500K 2SMD 210 LAMP INCAND RT-1 WIRE TERM 12V 211 LAMP INCAND RT-1.75 BI-PIN 14V 212 LAMP INCAN RT-1.75 WIRE TERM 28V 213 LAMP INCAND RT-1.75 BI-PIN 28V 214 505NM 30DEGREE 5MM PLASTIC 215 LED BLUE-GREEN CLEAR T-1 3/4 T/H 216 LED BLUE-GREEN DIFF ICE CUBE T/H 217 LED GREEN/RED/YLW CLR 4PLCC SMD 218 LED GREEN/RED/YLW CLR 4PLCC SMD 219 LED SUNPLUS 35 PURPLE SMD 220 LED LUXEON REBEL LIME SMD Unit Price $1.99 $1.14 $1.63 $6.26 $1.87 $2.11 $1.10 $1.07 $1.24 $1.26 $2.31 $1.07 $1.89 $2.00 $2.57 $1.07 $1.31 $2.25 $1.70 $3.94

Answer 1

So, to find out data directly from another sheet you can use VLOOKUP function in excel

In this case, click on the cell where you want the data to be looked up

Type the formula = VLOOKUP

it will first ask for lookup value, in this case your look value will be the description, once you'll specify the description, it will look up for the similar description from the another sheet. So, click on the cell where the corresponding description is mentioned.

Next it will ask for table array, where you have to specify the from where you want the data tom be looked up i.e, select the entire data in another sheet

Then, you've to specify the column number which means if you want to look up for unit price from another sheet specify column number as 3.

Next, you've to specify whether you need exact match where FALSE =exact match and TRUE= approximate match.

I hope I've answered correctly. Please reply in comment section if you need any other help.