Find a value in a row from different sheet and return a value.

[mymachix]

Hello Excel Angels,


I have two Sheets;

Sheet A
Sheet B

in Sheet A, there's a lot of data arranged in Rows

1.PNG


In Sheet B, I need to fill in Data in Column C and D base in data from Sheet A.

2.PNG

Requirement:

In Sheet B column C, I need to return a value of "FXS" if there's an "FXS" value in Sheet A row 2 and "FXO" if the value found is "FXO".
In Sheet B column D, I need to return a value of "Registered" if there's a value "Registered" found in Sheet A row 2. and "Unregistered" or "FXO" if these are found. (none of these 3 exists in same row)

Please help and thank you in advance

[sbkeenan]

Hi mymachix

Not sure I entirely understand what you're getting at, but it sould like a VLOOKUP to me. Have you tried that?

Regards
Stephen.

[Narasanna]

hi mymachix,

The outcome you are expecting will get by using the VLOOKUP formula. try once.

[mymachix]

Yes but can't find a way to make it work in VLOOKUP

what i mean is, i need to check if the following values exists in the sheet A by ROWS (not by column) and then return the same value in Sheet B if found.

FXS
FXO
Registered
Unregistered
Unknown


Please help..

[sbkeenan]

Hi mymachix

There is an HLOOKUP, which does exactly the same thing as the VLOOKUP, except it works with horizontal rows, rather than vertical columns.

If you could post a sample spreadsheet, it would make it easier to help you.

Regards
Stephen.

[mymachix]

Hi,

I have attached a sample file. I;m familliar in VLOOKUP but can't make it work in ROWS not sure why. In the attached file,I need to return a value in SheetB if that value is found in SheetA rows. For example,IF word "Apple" is found in A2 to AZ2 of SheetA, return "Apple" in C2 of SheetB .




Anyone can help please?

[sbkeenan]

Hi

I'm still not entirely sure what you're trying to achieve, but my understanding is this:

If a row in sheet A contains either FSX or FSO, then put FSX or FXO in column C on sheet B for the corresponding row.

If a row in Sheet A contains either Registered or Unregistered, then place Registered or Unregistered in column D in Sheet B for the corresponding row.

If this is the case, I have attached a sample spreadsheet which does that.

You should note that Column C on Sheet B is blank because the are no FSX or FSO values at all in Sheet A.

I have modified the formulea such that if teh desired values are not found, leave the cell blank rather than populate it with #N/A.

Hope this is what you are after.

REgards
Stephen.

[mymachix]

Wow..i'm amazed on the formula used. thank you very much. (will take time before i learn and understand it).. If I need to add one more condition, expample: "Unknown" , how do I adjust or add it to the formula?

=IF(NOT(ISNA(HLOOKUP("Registered",SheetA!8:8,1,FALSE))),HLOOKUP("Registered",SheetA!8:8,1,FALSE),IF(NOT(ISNA(HLOOKUP("Unregistered",SheetA!8:8,1,FALSE))),HLOOKUP("Unregistered",SheetA!8:8,1,FALSE),""))

thanks a lot!!

[mymachix]

Got it!!!

Simply put the unknown inside the last ""

Thank you very much!!

[sbkeenan]

Hi mymachix

If I understand you correctly, it should simply be a case of placing the text 'Unknown' between the "" at the end of the formula in each column so that it reads like this:

Column B:

=IF(NOT(ISNA(HLOOKUP("FXS",SheetA!7:7,1,FALSE))),HLOOKUP("FXS",SheetA!7:7,1,FALSE),IF(NOT(ISNA(HLOOKUP("FXO",SheetA!7:7,1,FALSE))),HLOOKUP("FXO",SheetA!7:7,1,FALSE),"Unknown"))

Column C:

=IF(NOT(ISNA(HLOOKUP("Registered",SheetA!2:2,1,FALSE))),HLOOKUP("Registered",SheetA!2:2,1,FALSE),IF(NOT(ISNA(HLOOKUP("Unregistered",SheetA!2:2,1,FALSE))),HLOOKUP("Unregistered",SheetA!2:2,1,FALSE),"Unknown"))

The above will work as before, except where it doesn't find FXS or FSO, it will place 'Unknown' in Column C. Similarly, if it doesn't find 'Registered' or 'Unregistered', it will place 'Unknown' in column D.

Is this what you mean?

Regards
Stephen.