How do I create a formula to look up, match, then return a number from another column

I'll try to explain my problem as best I can.

On Sheet1, I have a combined list of Tag numbers/Plate Numbers (column A), and ID number in Column B (B is empty).

On Sheet2, Column A is Tag Number, Column B is Plate Number, and Column C is ID number (filled with ID numbers).

I'm trying to create a formula that looks at Sheet1 column A, finds a match on Sheet2 from either Column A or B, and then returns ID Number from Column C, into the empty Column B in Sheet1.

I'm sure this is a simple formula, but I can't wrap my brain around it.

Thanks in advance for you help

Welcome to the forums, Robin Hood!

Two nested IF VLOOKUPS should do the trick for you.

Welcome to the forum

Try this...

	A	B	C	D	E	F
1	Tag/Plate	ID		Tag	Plate	ID
2	aa11	ww		aa	11	ww
3	bb22	xx		bb	22	xx
4	cc33	yy		cc	33	yy
5	dd44	zz		dd	44	zz

B2=INDEX($F$2:$F$5,MATCH(A2,INDEX($D$2:$D$5&$E$2:$E$5,0),0))
copied down

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Sheet1:
A2 is TagPlate
B2 is ID:

=IFERROR(LOOKUP(2,1/(Sheet2!$A$2:$A$5&Sheet2!$B$2:$B$5=A2),Sheet2!$C$2:$C$5),"Not found")

Untitled1.png

try

B2

Formula:

Please Login or Register  to view this content.

Have not (yet) looked at the sample file, but sometimes, all it takes to make a formula work, is to "changed minor items". There is no "way" of the forum, all members here contribute freely of their time and knowledge, and 1 of the bigger problems of written word is that 1 person understands a comment 1 way, and someone else may interpret in a completely different way. So 1 member may read your question and not be sure of what you are asking, while another member may immediately see exactly what you mean and want

Also... the screen does not auto-update. So it is possble that Shukla didn't see the earlier post until after he posted. Right now, I can't open your file, so I can not tell which (if either) is correct.

In any event, smart-alec comments are rarely appreciated here and are best avoided.

No prblems. We all get bad hair days. Me more often than most....

Did the solution (s) offered do the job for you?

This formula worked. Thank you so very much !!!