Using an if(match statement?

[jackjack583]

Hey guys, what I'm trying to do is the following:

I want to make sure that if A1 matches any value within this range B1:B1826 (on a different sheet) then return the column C in that row.

For example, if A1=B453 return C453

Any help would be much appreciated!
Thank you

 

[wideboydixon]

Assuming your reference table is on Sheet2 then formula for B1:

=IFERROR(VLOOKUP($A1,Sheet2!$B$1:$C$1826,2,FALSE),"")

WBD

 

[jackjack583]

Not quite working for some reason

 

[wideboydixon]

Then you'll need to provide a bit more information:

[1] Where the lookup value is (assuming A1)
[2] Where you want the result
[3] The name of the sheet on which the lookup table lives
[4] The error you're getting with the above formula

Thanks

WBD

 

[jackjack583]

ADVERTISEMENT

If B1 in sheet 1 matches a cell in column A of sheet 2 then return the column E of that matching row in sheet 2.

Formula input would be in cell P2 of sheet 1 -> IF B2 (Sheet 1) = A2:A1826 (any value in that range of sheet 2) then return C(matching row number sheet 2)

See what I'm saying? Hard to explain sorry haha

 

[jackjack583]

"Sheet 1" and "Sheet 2"

 

[wideboydixon]

ADVERTISEMENT

If B1 in sheet 1 matches a cell in column A of sheet 2 then return the column E of that matching row in sheet 2.

Formula input would be in cell P2 of sheet 1 -> IF B2 (Sheet 1) = A2:A1826 (any value in that range of sheet 2) then return C(matching row number sheet 2)

See what I'm saying? Hard to explain sorry haha

You're contradicting yourself and it doesn't match with your original post. Please clarify:

[1] The name of the sheet that contains the lookup table. Excel normally creates "Sheet2" but you're suggesting it's "Sheet 2". Does it really have the space?
[2] The column the contains the value to search for. Originally you said column A but now you've changed that to column B. Which is it?
[3] In which column you want to put the result of the lookup. Originally I'd assumed column B but now that's potentially the column that contains the value to search for.
[4] The column on "Sheet2"/"Sheet 2" that contains the value you want to return. Your original post said column C but you changed it to column E and then back to column C in the above.

This is a simple VLOOKUP you're looking for but your requirements are not clear at all. Posting sample data would make it much much easier.

WBD

 

[jackjack583]

Ignore the first post, let's say we have "Sheet1" if you will and "Sheet2" (No space)

In cell P2 on "Sheet2" is where i'll be inputing the formula. I want to cross-reference cell B2 in "Sheet1" to see if in column A (range A2:A1875) of "Sheet2" there is a match. And if there is a match I want it to return in cell P2 the value that is found in the cell with matching row and column E of "Sheet2"

I'll give an example with values ->

Let's say "Sheet1" cell B2 = 4589 -> Now in "Sheet2" cell A279 = 4589 so here there's a match. I would like it then to return the value inside cell E279.

 

[jackjack583]

SORRY I'll be inputing the formula in cell P2 "Sheet1"! Apologies

 

[wideboydixon]

OK. Formula to put in P2 on Sheet1:

=IFERROR(VLOOKUP($B2,Sheet2!$A$2:$E$1875,5,FALSE),"Not Found")

You can change the "Not Found" to "" if you just want it blank.

WBD