Look Up Function

[Whitney]

What function do I use if I have two spreadsheets and I want the master
spreadsheet to look up data on a second spreadsheet, if the data is on the
master I need it to make the record. Also, what options do I have to "mark"
the record, highlight row, change the font color, add a charachter to another
cell at the end of the row, etc.?

 

[Atreides]

Hi Whitney,

For a simple method, try using LOOKUP, VLOOKUP or HLOOKUP. The formula
wizard should help you out. However these rely on the data you are looking up
being sorted in alphabetical order. If you'd like something more robust (but
a bit more complicated) try a combination of MATCH and INDEX. MATCH will find
the data you are after (regardless of whether it's sorted) but only returns a
row number. INDEX will then find the correct data you are after by using that
row number. For example =INDEX(result range,MATCH(lookup value,lookup
range,0))

As for "marking" the record, go to the Format menu and choose Conditional
Formatting. You can then set the Format of the current cells based on what's
in the current cells or even based on other cells. It might take a bit of
playing around to get it to do exactly what you want though (especially the
subtle differences between relative and absolute references)

- Atreides

 

[Whitney]

I guess I just need it to say Yes or No. I would liket Mastersheet to look at
Sheet2 and just say Yes if on the sheet and leave blank if not on the sheet


Mastersheet:
A B
Order ID Result (Y)

Sheet2
A
Order ID

 

[Atreides]

Like this?:

Mastersheet
A B
1 aaa Y
2 bbb Y
3 ccc

Sheet 2
A
1 aaa
2 bbb

The formula in B1 would be:

=IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$2,0)),"Y","")

And so on...

Hope this helps

- Atreides

 

[Whitney]

yes, exactly. It worked perfectly. Thank you