This one is tricky. VLookup but return a different cell value

[edjohn20]

Okay, so I am using Vlookup to compare two tables. For argument sake I am comparing two addresses. If they match the vlookup simply returns the same address as the matched address. This is where I am stuck. I need to return a location number for the matching address instead. For the life of me I cannot figure this out. I know it should be easy but I can't seem to get the logic right for excel. Here is some data to work with.

Formula for VLookup: =VLOOKUP(D70,'Update sysID'!G$1:G$8953,1,FALSE) This compares the address in my table (columnD) to the table array "Update sysID" and when it finds a matching address it just displays it. In Update sysID table, column F contains an ORGID that I want to display instead. How would the formula work? So lets say that it finds the address in row 3234 and the addresses match then I want it to return the value for F3234 instead of the matching address in G3234. The data is all contained in the same workbook. The vlookup is located on "address match" sheet

 

[JMWh1t3]

Not sure I'm reading it right, but would XLOOKUP work?

=XLOOKUP(D70,'Update sysID'!G$1:G$8953,'Update sysID'!F$1:F$8953,FALSE)

This would look for the value in D70 in sysID column G and returns the matching value in column F

 

[edjohn20]

Not sure I'm reading it right, but would XLOOKUP work?

=XLOOKUP(D70,'Update sysID'!G$1:G$8953,'Update sysID'!F$1:F$8953,FALSE)

This would look for the value in D70 in sysID column G and returns the matching value in column F

Ah, yes that does work!!! now how would I take it one step further. Let's say the value in D1 is not found at all and I want to compare a different column such as C1 and if that is found then display matching value from F

 

[edjohn20]

I did this, it seems to work

=XLOOKUP(C1,'Update NamsysID'!G$1:G$8953,'Update NamsysID'!F$1:F$8953,XLOOKUP(D1,'Update NamsysID'!G$1:G$8953,'Update NamsysID'!F$1:F$8953),FALSE)

 

[Alex Blakenburg]

I did this, it seems to work

=XLOOKUP(C1,'Update NamsysID'!G$1:G$8953,'Update NamsysID'!F$1:F$8953,XLOOKUP(D1,'Update NamsysID'!G$1:G$8953,'Update NamsysID'!F$1:F$8953),FALSE)

That is the reverse of what you previously indicated.
That formula looks for C1 first and if that fails looks for D1. Based on you previous post you want it the other way around ie look for D1 first if that fails look for C1.

The alternate lookup utilises the "if_not_found" position.
=xlookup(lookup_value, lookup_array, return_array, if_not_found)

 

[edjohn20]

That is the reverse of what you previously indicated.
That formula looks for C1 first and if that fails looks for D1. Based on you previous post you want it the other way around ie look for D1 first if that fails look for C1.

The alternate lookup utilises the "if_not_found" position.
=xlookup(lookup_value, lookup_array, return_array, if_not_found)

yeah it is, while running it i decided I wanted to compare c before d for better results

 

[Alex Blakenburg]