Excel 2003 reverse Lookup

[adulted]

Hello All,

I have a vehicle repair excel sheet which I've put together over several months. vlookup works well, however with over a couple hundred items in the PartTbl list, I would never remember the number to Type for the lookup the service required.

In A21 I place the number 3, In cell B21 The formula =IF(A21="","",VLOOKUP(A21,PartTbl,2)) returns the value Air Conditioning.

In cell A22 I place the number 2, In cell B22 the formula =IF(A22="","",VLOOKUP(A22,PartTbl,2)) returns the value AirBag

In cell A23 I place the number 1. In cell B23 the formula =IF(A23="","",VLOOKUP(A23,PartTbl,2)) returns the value Labour.

Is there a way possibly using Data Validation or something to look in the column B that I could see the service required and the number appears in Column A

Here is a short clip of those cells.

Excel 2003
	A	B	C	D	E	F	G	H
20	Task	PARTNAME			PART#	QTY	UNITPRICE	AMOUNT
21	3	Air Conditioner			acunit	1	109.00	109.00
22	2	Airbag			G50 - 15"	1	25.00	25.00
23	1	Labour			Per Hour	2	75.00	150.00
AutoRepair

Cell Formulas
Range	Formula
G21	=IF(A21="","",VLOOKUP(A21,PartTbl,4))
G22	=IF(A22="","",VLOOKUP(A22,PartTbl,4))
G23	=IF(A23="","",VLOOKUP(A23,PartTbl,4))
H21	=IF(F21="","",G21*F21)
H22	=IF(F22="","",G22*F22)
H23	=IF(F23="","",G23*F23)
B21	=IF(A21="","",VLOOKUP(A21,PartTbl,2))
B22	=IF(A22="","",VLOOKUP(A22,PartTbl,2))
B23	=IF(A23="","",VLOOKUP(A23,PartTbl,2))
E21	=IF(A21="","",VLOOKUP(A21,PartTbl,3))
E22	=IF(A22="","",VLOOKUP(A22,PartTbl,3))
E23	=IF(A23="","",VLOOKUP(A23,PartTbl,3))

Named Ranges
Name	Refers To	Cells
PartTbl	='E:\Software Samples\Excel\automotive\Bill Saunders\[Bill Saunders Car Repair - Copy 20.xls]Services'!$A$4:$F$178

Excel 2003
	A	B	C	D	E	F
3	Task	PART NAME	PART #	Cost	MarkUp	Total
4	1	Labour	Per Hour	75	11.25	86.25
5	2	Airbag	G50 - 15"	25	3.75	28.75
6	3	Air Conditioner	acunit	109	16.35	125.35
Services

Cell Formulas
Range	Formula
E4	=D4*$G$4
E5	=D5*$G$4
E6	=D6*$G$4
F4	=D4+E4
F5	=D5+E5
F6	=D6+E6

Thanks in advance
Paul

 

[Peter_SSs]

Try this

Excel Workbook
	A	B
21	2	Airbag
22	3	Labour
23	1	Air Conditioner
24
25
26
27
Lookup

#VALUE!

 

[adulted]

Hello Peter_SSs,

Hmm very interesting. I placed your formulas as suggested and copied them down 2 cells. It did work, however your formula doesn't reflect the services I need to lookup.

Ideally, I would like to be able to use either Data Validation, or type in the service that will reflect on the Services I need. Otherwise I have to refer to the Services tab and remember the numbers for the service required.

Excel 2003
	A	B	C	D	E	F	G	H
21	18	Baby Seat			EAYM	1	25.00	25.00
22	19	Back-Up Lights			DOPF	1	24.00	24.00
23	2	Airbag			G50 - 15"	2	25.00	50.00
AutoRepair

Cell Formulas
Range	Formula
G21	=IF(A21="","",VLOOKUP(A21,PartTbl,4))
G22	=IF(A22="","",VLOOKUP(A22,PartTbl,4))
G23	=IF(A23="","",VLOOKUP(A23,PartTbl,4))
H21	=IF(F21="","",G21*F21)
H22	=IF(F22="","",G22*F22)
H23	=IF(F23="","",G23*F23)
A21	=IF(B21="","",INDEX(PartTbl,MATCH(B21,INDEX(PartTbl,0,2),0),1))
A22	=IF(B22="","",INDEX(PartTbl,MATCH(B22,INDEX(PartTbl,0,2),0),1))
B21	=INDEX(PartTbl,0,2)
B22	=INDEX(PartTbl,0,2)
B23	=IF(A23="","",VLOOKUP(A23,PartTbl,2))
E21	=IF(A21="","",VLOOKUP(A21,PartTbl,3))
E22	=IF(A22="","",VLOOKUP(A22,PartTbl,3))
E23	=IF(A23="","",VLOOKUP(A23,PartTbl,3))

Named Ranges
Name	Refers To	Cells
PartTbl	='E:\Software Samples\Excel\automotive\Bill Saunders\[Bill Saunders Car Repair - Copy 20.xls]Services'!$A$4:$F$178

In trust
Paul

 

[Peter_SSs]

Look back at my last post again. The information for cell B21 is a Data Validation formula, not a cell formula.

Select B21 then go in to the Data Validation dialog. In the Settings tab choose Allow: List and put the formula I suggested in the 'Source:' box

You can copy the validation to the other column B cells or you could actually select them all first before applying the DV.

 

[adulted]

Hello Peter_SSs,

Wow I guess I should be looking into a grammar or reading course.

AMAZING!
MrExcel ROCKS ! ! ! !
Paul

 

[Peter_SSs]

Glad it worked for you. Thanks for letting us know.